-- @owner:m0_68540079
-- @date:2025/4/21
-- @testpoint:TO_NUMBER在不同使用存储例程（Stored Routines）中的使用情况，expect：若入参1错误则入参2生效，若都错误，则合理报错
--step1：在函数体中使用TO_NUMBER函数；expect：正常输出或合理报错
DROP FUNCTION IF EXISTS CASTFUNC;
CREATE OR REPLACE FUNCTION CASTFUNC (
    input_string IN VARCHAR2,
    DEFAULTVAL  IN INTEGER DEFAULT 123
) RETURN VARCHAR2
IS
BEGIN

RETURN TO_NUMBER(input_string DEFAULT 123 ON CONVERSION ERROR);
END;
/

SELECT CASTFUNC(123);

DROP FUNCTION IF EXISTS CASTFUNC;

--step2：在存储过程中使用TO_NUMBER函数；expect：正常输出或合理报错
DROP TABLE IF EXISTS sp;
CREATE TABLE sp (
                    test varchar2(20)
);

CREATE OR REPLACE PROCEDURE convert_to_number (
    p_input_number IN integer  -- 输入数值
)
IS
BEGIN
INSERT INTO sp
SELECT TO_NUMBER(p_input_number) ;
END;
/

CALL convert_to_number(123);

SELECT * FROM SP;

DROP TABLE IF EXISTS sp;

--step3：在package中使用TO_NUMBER函数；expect：正常输出或合理报错
DROP TABLE IF EXISTS sp;

DROP PACKAGE IF EXISTS string_operations;

CREATE TABLE sp (
                    test integer
);

CREATE OR REPLACE PACKAGE string_operations AS
    PROCEDURE convert_to_number (
        p_input_number IN integer  -- 输入数值
    );
END string_operations;
/

CREATE OR REPLACE PACKAGE BODY string_operations AS
    PROCEDURE convert_to_number (
        p_input_number IN integer
    ) IS
    BEGIN
INSERT INTO sp (test)
VALUES (TO_NUMBER(p_input_number));

COMMIT;
END convert_to_number;

BEGIN
NULL;
END string_operations;
/

BEGIN
string_operations.convert_to_number('123');
END;
/

SELECT * FROM sp;

DROP TABLE IF EXISTS sp;

DROP PACKAGE IF EXISTS string_operations;

--step4：在触发器中使用TO_NUMBER函数；expect：正常输出或合理报错(触发器无法利用jdbc进行使用)
DROP table IF EXISTS employees;

DROP TRIGGER IF EXISTS trg_lower_case_names on employees;

CREATE TABLE employees (
                           employee_id NUMBER PRIMARY KEY,
                           first_name VARCHAR2(50),
                           last_name VARCHAR2(50),
                           salary NUMBER
);

CREATE OR REPLACE FUNCTION casttrigger()
    RETURNS trigger AS $$
BEGIN
    NEW.first_name := TO_NUMBER(NEW.first_name);
    NEW.last_name := TO_NUMBER(NEW.last_name);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_lower_case_names
    BEFORE INSERT ON employees
    FOR EACH ROW
EXECUTE PROCEDURE casttrigger();

INSERT INTO employees (employee_id,first_name, last_name,salary) VALUES (1,'123', '234',123);
INSERT INTO employees (employee_id,first_name, last_name,salary) VALUES (2,'345', '456',234);

SELECT * FROM employees;

DROP table IF EXISTS employees;

DROP TRIGGER IF EXISTS trg_lower_case_names on employees;

--step5：在触发器中使用TO_NUMBER函数；expect：正常输出或合理报错
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
                           employee_id NUMBER PRIMARY KEY,
                           first_name VARCHAR2(50),
                           last_name VARCHAR2(50),
                           salary NUMBER
);

INSERT INTO employees (employee_id,first_name, last_name,salary) VALUES (1,'123', '234',123);
INSERT INTO employees (employee_id,first_name, last_name,salary) VALUES (2,'345', '456',234);

PREPARE search_employee (TEXT) AS
    SELECT * FROM employees WHERE TO_NUMBER(first_name) = TO_NUMBER($1);

EXECUTE search_employee('123');

DEALLOCATE search_employee;

DROP TABLE IF EXISTS employees;

